﻿
/*===============================================================================
MTSL_View_PerfmonRawInstanceNamesAndIds

LoadTestRunId	InstanceId	InstanceName
11				0			CONTROLLER\Memory\Page Faults/sec\systemdiagnosticsperfcounterlibsingleinstance
11				390			CONTROLLER\Process\Thread Count\QTAgent32
===============================================================================*/
	--START CODE--
	CREATE VIEW MTSL_View_PerfmonRawInstanceNamesAndIds AS
	---------------------------------------------------------------------------------

		SELECT 
			instance.LoadTestRunId
			,instance.InstanceId			
			,(
				category.MachineName +
				'\' + category.CategoryName
				+ '\' + counter.CounterName
				+ '\' + instance.InstanceName
			) AS InstanceName

		FROM LoadTestPerformanceCounterCategory AS category 

		INNER JOIN LoadTestPerformanceCounter AS counter 
			ON category.LoadTestRunId = counter.LoadTestRunId
			AND category.CounterCategoryId = counter.CounterCategoryId

		INNER JOIN LoadTestPerformanceCounterInstance AS instance 
			ON counter.CounterId = instance.CounterId
			AND counter.LoadTestRunId = instance.LoadTestRunId
	GO
/*===============================================================================
MTSL_View_PerfmonTransactionNamesAndIDs

LoadTestRunId	InstanceId	CounterName			TransInstanceName	MachineName
11				1200		Total Transactions	_Total				CONTROLLER
11				1517		Total Transactions	HomePage(000)		CONTROLLER
===============================================================================*/
	--START CODE--
	CREATE VIEW MTSL_View_PerfmonTransactionNamesAndIDs AS
	---------------------------------------------------------------------------------

		SELECT 
			instance.LoadTestRunId
			,instance.InstanceId
			,counter.CounterName
			,instance.InstanceName AS TransInstanceName
			,category.MachineName
			--,(
			--	'\' + category.CategoryName
			--	+ '\' + counter.CounterName
			--	+ '\' + instance.InstanceName
			--) AS InstanceName

		FROM LoadTestPerformanceCounterCategory AS category 

		INNER JOIN LoadTestPerformanceCounter AS counter 
			ON category.LoadTestRunId = counter.LoadTestRunId
			AND category.CounterCategoryId = counter.CounterCategoryId
			--AND counter.CounterName = 'Transactions/Sec'
			AND category.CategoryName = 'LoadTest:Transaction'

		INNER JOIN LoadTestPerformanceCounterInstance AS instance 
			ON counter.CounterId = instance.CounterId
			AND counter.LoadTestRunId = instance.LoadTestRunId
	GO
/*===============================================================================
TSL_View_PerfmonInstanceIdsToTestIds_Avg

LoadTestRunId	PerfmonInstanceId	InstanceName					TestCaseId
2121			10315				Immunization_Insurance(000)		000
2121			10316				Immunization_SelfPay(001)		001
===============================================================================*/
	CREATE VIEW TSL_View_PerfmonInstanceIdsToTestIds_Avg AS

		SELECT InstanceTable.LoadTestRunId
			  ,InstanceId AS PerfmonInstanceId
			  ,InstanceTable.InstanceName
			  --,CAST(SUBSTRING(InstanceTable.InstanceName, CHARINDEX('(', InstanceTable.InstanceName) + 1, 3) AS int) AS TestCaseId
			  ,SUBSTRING(RIGHT(InstanceTable.InstanceName, CHARINDEX('(', REVERSE(InstanceTable.InstanceName)) - 1), 1, 3) AS TestCaseId
		FROM LoadTestPerformanceCounterInstance AS InstanceTable
		
		INNER JOIN LoadTestPerformanceCounter AS Counter
			ON InstanceTable.CounterId = Counter.CounterId
			AND InstanceTable.LoadTestRunId = Counter.LoadTestRunId
			AND Counter.CounterName = 'Avg. Test Time'
			AND InstanceTable.InstanceName != '_Total'
			AND InstanceTable.InstanceName NOT LIKE '_Agent%'
	GO
/*===============================================================================
TSL_View_PerfmonInstanceIdsToTestIds_Avg

LoadTestRunId	PerfmonInstanceId	InstanceName	TransactionId
11				1518				HomePage(000)	000
11				1537				Expand1(001)	001
===============================================================================*/
	CREATE VIEW TSL_View_PerfmonInstanceIdsToTransactionIds_Avg AS

		SELECT Instance.LoadTestRunId
			  ,InstanceId AS PerfmonInstanceId
			  ,InstanceName
			  ,SUBSTRING(RIGHT(InstanceName, CHARINDEX('(', REVERSE(InstanceName)) - 1), 1, 3) AS TransactionId
		  FROM LoadTestPerformanceCounterInstance AS Instance
			  INNER JOIN LoadTestPerformanceCounter AS Counter
			  ON Instance.CounterId = Counter.CounterId
			  AND Instance.LoadTestRunId = Counter.LoadTestRunId
			  AND CounterName = 'Avg. Transaction Time'
		  AND InstanceName != '_Total'
	GO
/*===============================================================================
TSL_View_PerfmonInstanceIdsToPageIds_Avg

LoadTestRunId	PerfmonInstanceId	InstanceName				PageId
2121			10376				Signin Page(000)			000
2121			10377				default-aspx{GET}(002)		002

This view filters the Perfmon Instance table to get only instances that are
from the 'Avg Page Time' counter. It then strips out the page ID from the 
name. The embedded 3 digit number is the page ID.
===============================================================================*/
	--START CODE--
	CREATE VIEW TSL_View_PerfmonInstanceIdsToPageIds_Avg AS

		SELECT InstanceTable.LoadTestRunId
			  ,InstanceTable.InstanceId AS PerfmonInstanceId
			  ,InstanceTable.InstanceName
			  ,SUBSTRING(RIGHT(InstanceTable.InstanceName, CHARINDEX('(', REVERSE(InstanceTable.InstanceName)) - 1), 1, 3) AS PageId
		FROM LoadTestPerformanceCounterInstance AS InstanceTable
		
		INNER JOIN LoadTestPerformanceCounter AS Counter
			  ON InstanceTable.CounterId = Counter.CounterId
			  AND InstanceTable.LoadTestRunId = Counter.LoadTestRunId
			  AND CounterName = 'Avg. Page Time'
		  AND InstanceTable.InstanceName != '_Total'
	GO
/*===============================================================================
TSL_View_LoadTestSummary

This view provides a single list of all items from the LoadTestRun and
TSL_LoadTestCoreInfo tables. This view is where the Reporting Tool gets
the bulk of the Summary Data it provides
===============================================================================*/
	--START CODE--
	CREATE VIEW TSL_View_LoadTestSummary AS
		SELECT a.LoadTestRunId
			  ,LoadTestName
			  ,Description
			  ,StartTime
			  ,EndTime
			  ,RunDuration
			  ,WarmupTime
			  ,RunSettingUsed
			  ,IsLocalRun
			  ,ControllerName
			  ,Outcome
			  ,CooldownTime
			  ,AgentCount
			  ,MaxUsers
			  ,SampleInterval
			  ,TotalTests
			  ,PassedTests
			  ,FailedTests
			  ,AvgTT_All
			  ,TestsSec_All
			  ,TotalTrans
			  ,AvgTrET_All
			  ,AvgTrRT_All
			  ,TransSec_All
			  ,TotalPages
			  ,PagesSec_All
			  ,AvgPgT_All
			  ,TotalRequests
			  ,PassedRequests
			  ,FailedRequests
			  ,CachedRequests
			  ,ReqSec_All
			  ,AvgReqT_All
			  ,AvgReqLen_All
			  ,AvgTT_Pass
			  ,TestsSec_Pass
			  ,AvgTrET_Pass
			  ,AvgTrRT_Pass
			  ,TransSec_Pass
			  ,PassedPages
			  ,PagesSec_Pass
			  ,AvgPgT_Pass
			  ,PctTime_WebTest
			  ,PctTime_Rules
			  ,PctTime_LoadPlugin
			  ,FLAG_ContainsPages
			  ,FLAG_ContainsTransactions
			  ,FLAG_ContainsDetailedTimingInfo
			  ,FLAG_ContainsStepLoadProfile
			  ,FLAG_TestAborted
			  ,FLAG_LoadTestParsingFailed
		      
		  FROM TSL_LoadTestCoreInfo AS a
		INNER JOIN LoadTestRun AS b ON a.LoadTestRunId = b.LoadTestRunId  		
	GO

-- Below Views already moved to new VSLT_01_Views

/*===============================================================================
MTSL_View_PerfmonInstanceNamesAndIds

LoadTestRunId	InstanceId	InstanceName									MachineName		CategoryName	CounterName			Instance
11				0			\\CONTROLLER\Memory\Page Faults/sec				CONTROLLER		Memory			Page Faults/sec	
11				390			\\CONTROLLER\Process(QTAgent32)\Thread Count	CONTROLLER		Process			Thread Count		QTAgent32
===============================================================================*/
	--START CODE--
	CREATE VIEW MTSL_View_PerfmonInstanceNamesAndIds AS
	---------------------------------------------------------------------------------
		SELECT 
			instance.LoadTestRunId
			,instance.InstanceId
			,(
				'\\' + category.MachineName
				+ '\' + category.CategoryName
				+	case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
						 then ''
						 else '(' + instance.InstanceName  + ')'
						 end
				+ '\' + counter.CounterName
			) AS InstanceName
			,category.MachineName
			,category.CategoryName
			,counter.CounterName
			,(case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
						 then ''
						 else instance.InstanceName
						 end) AS Instance
			,instance.LoadTestItemId

		FROM LoadTestPerformanceCounterCategory AS category 

		INNER JOIN LoadTestPerformanceCounter AS counter 
			ON category.LoadTestRunId = counter.LoadTestRunId
			AND category.CounterCategoryId = counter.CounterCategoryId

		INNER JOIN LoadTestPerformanceCounterInstance AS instance 
			ON counter.CounterId = instance.CounterId
			AND counter.LoadTestRunId = instance.LoadTestRunId
	GO
/*===============================================================================
TSL_View_PageNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				RequestId	RequestUri				RequestNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	18			ReportServer-Homepage1	ReportServer-Homepage1(018)
===============================================================================*/
	--START CODE--
	CREATE VIEW TSL_View_PageNamesWithIDs AS
		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName, RequestId, RequestUri 
			  ,RequestUri + '(' + RIGHT ('000'+ CAST(RequestId AS nvarchar(3)), 3) + ')' AS RequestNameWithId
		FROM dbo.WebLoadTestRequestMap AS a

		INNER JOIN LoadTestCase AS b 
			ON a.LoadTestRunId = b.LoadTestRunId AND a.TestCaseId = b.TestCaseId

		INNER JOIN LoadTestScenario AS c 
			ON b.LoadTestRunId = c.LoadTestRunId AND b.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
TSL_View_TestNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				TestNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	Single Project_ViewAll_OFF(000)
12				0			Scenario1		0			Single Project_ViewAll_OFF	Single Project_ViewAll_OFF(000)
===============================================================================*/
	--START CODE--
	CREATE VIEW TSL_View_TestNamesWithIDs AS

		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName 
			  ,TestCaseName + '(' + RIGHT ('000'+ CAST(TestCaseId AS nvarchar(3)), 3) + ')' AS TestNameWithId
		FROM LoadTestCase AS a

		INNER JOIN LoadTestScenario AS c 
			ON a.LoadTestRunId = c.LoadTestRunId AND a.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
TSL_View_TransactionNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				TransactionId	TransactionName	TransactionNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	0				HomePage		HomePage(000)
11				0			Scenario1		0			Single Project_ViewAll_OFF	1				Expand1			Expand1(001)
===============================================================================*/
	--START CODE--
	CREATE VIEW TSL_View_TransactionNamesWithIDs AS

		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName, TransactionId, TransactionName 
			  ,[TransactionName] + '(' + RIGHT ('000'+ CAST([TransactionId] AS nvarchar(3)), 3) + ')' AS TransactionNameWithId
		FROM [WebLoadTestTransaction] AS a

		INNER JOIN LoadTestCase AS b 
			ON a.LoadTestRunId = b.LoadTestRunId AND a.TestCaseId = b.TestCaseId

		INNER JOIN LoadTestScenario AS c 
			ON b.LoadTestRunId = c.LoadTestRunId AND b.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
TSL_View_PageSummary
===============================================================================*/
	--START CODE--
	CREATE VIEW TSL_View_PageSummary AS
	SELECT 
		pageSummaryData.LoadTestRunId
		,pageSummaryData.PageId
		,testCase.TestCaseId
		,scenario.ScenarioId
		,scenario.ScenarioName
		,testCase.TestCaseName
		,requestMap.RequestUri
		, pageSummaryData.PageCount
		, pageSummaryData.Average
		, pageSummaryData.Minimum
		, pageSummaryData.Maximum
		, pageSummaryData.Percentile90
		, pageSummaryData.Percentile95
		, pageSummaryData.Percentile99
		, pageSummaryData.Median
		, pageSummaryData.StandardDeviation  
	FROM LoadTestPageSummaryData AS pageSummaryData

	INNER JOIN WebLoadTestRequestMap AS requestMap 
		ON pageSummaryData.LoadTestRunId = requestMap.LoadTestRunId
		AND pageSummaryData.PageId = requestMap.RequestId

	INNER JOIN LoadTestCase AS testCase 
		ON pageSummaryData.LoadTestRunId = testCase.LoadTestRunId 
		AND requestMap.TestCaseId = testCase.TestCaseId

	INNER JOIN LoadTestScenario AS scenario 
		ON pageSummaryData.LoadTestRunId = scenario.LoadTestRunId 
		AND testCase.ScenarioId = scenario.ScenarioId
	GO

/*===============================================================================
TSL_View_TransactionSummary
===============================================================================*/
	--START CODE--
	CREATE VIEW TSL_View_TransactionSummary AS
	SELECT 
		transSummaryData.LoadTestRunId
		,transSummaryData.TransactionId
		,testCase.TestCaseId
		,scenario.ScenarioId
		,scenario.ScenarioName
		,testCase.TestCaseName
		,requestMap.TransactionName
		, transSummaryData.TransactionCount
		, transSummaryData.Average
		, transSummaryData.Minimum
		, transSummaryData.Maximum
		, transSummaryData.Percentile90
		, transSummaryData.Percentile95
		, transSummaryData.Percentile99
		, transSummaryData.Median
		, transSummaryData.StandardDeviation  
	FROM LoadTestTransactionSummaryData AS transSummaryData

	INNER JOIN WebLoadTestTransaction AS requestMap 
		ON transSummaryData.LoadTestRunId = requestMap.LoadTestRunId
		AND transSummaryData.TransactionId = requestMap.TransactionId

	INNER JOIN LoadTestCase AS testCase 
		ON transSummaryData.LoadTestRunId = testCase.LoadTestRunId 
		AND requestMap.TestCaseId = testCase.TestCaseId

	INNER JOIN LoadTestScenario AS scenario 
		ON transSummaryData.LoadTestRunId = scenario.LoadTestRunId 
		AND testCase.ScenarioId = scenario.ScenarioId
	GO
/*
LoadTestRunId
LoadTestName
Description
StartTime
RunDuration
RunSettingUsed
ControllerName
Outcome
SampleInterval
IsLocalRun
FLAG_TestAborted
FLAG_ContainsDetailedTimingInfo
FLAG_ContainsPages
FLAG_ContainsTransactions
LoadTest
Comment

*/
